﻿Public Class frmMain

    Private Sub miConnect_Click(sender As System.Object, e As System.EventArgs) Handles miConnect.Click
        Button.PerformClick()
        Try

            miDB.Items.Clear()
            Dim strSql As String = System.IO.File.ReadAllText("MS_Description_Sysdatabases.sql")
            'tbOutput.AppendText(String.Format("{0}" & vbCrLf, strSql))
            Dim cn As New SqlClient.SqlConnection(GetConnectionString(False))
            cn.Open()
            Dim sp As New SqlClient.SqlCommand(strSql, cn)
            Dim dr As SqlClient.SqlDataReader = sp.ExecuteReader()

            While dr.Read
                miDB.Items.Add(dr.Item("Name"))
            End While
            cn.Close()
            tbOutput.AppendText(String.Format("{0} - connect." & vbCrLf, miServer))
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

    End Sub

    Private Function GetConnectionString(withDBName As Boolean)
        Dim s As String
        If withDBName Then
            s = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};", miServer.Text, miDB.Text, miUser.Text, miPassword.Text)
        Else
            s = String.Format("Data Source={0};Initial Catalog=master;User Id={1};Password={2};", miServer.Text, miUser.Text, miPassword.Text)
        End If

        Return s
    End Function

    Private dt As New DataTable

    Private Sub miRefresh_Click(sender As System.Object, e As System.EventArgs) Handles miRefresh.Click
        Button.PerformClick()
        If miDB.Text = "" Then
            MsgBox("Select Database")
            Exit Sub
        End If
        Try
            dt.Clear()
            Dim strSql As String = System.IO.File.ReadAllText("MS_Description_List.sql")
            'tbOutput.AppendText(String.Format("{0}" & vbCrLf, strSql))

            Dim cn As New SqlClient.SqlConnection(GetConnectionString(True))
            cn.Open()
            Dim sp As New SqlClient.SqlCommand(strSql, cn)
            dt.Load(sp.ExecuteReader)
            dt.Columns("Description").ReadOnly = False
            dt.TableName = miDB.Text
            If dt.Columns("Check") Is Nothing Then
                dt.Columns.Add("Check", GetType(Boolean))
            End If
            dt.Columns("Check").ReadOnly = False
            BindingSource1.DataSource = dt

            cn.Close()
            tbOutput.AppendText(String.Format("{0} - refresh." & vbCrLf, miServer))
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub miApply_Click(sender As System.Object, e As System.EventArgs) Handles miApply.Click
        Button.PerformClick()
        If miDB.Text <> dt.TableName OrElse miDB.Text = "" Then
            MsgBox(String.Format("Database not correct! Change Database or press Refresh {0} Current DataBase = {1}", vbCrLf, dt.TableName))
            Exit Sub
        End If

        Dim strSqlTable As String = System.IO.File.ReadAllText("MS_Description_UpdateTable.sql")
        Dim strSqlCloumn As String = System.IO.File.ReadAllText("MS_Description_UpdateColumn.sql")
        Dim cn As New SqlClient.SqlConnection(GetConnectionString(True))
        cn.Open()

        For Each r As DataRow In dt.Rows
            If r!Check IsNot DBNull.Value AndAlso r!Check = True Then

                If r!ColumnName Is DBNull.Value Then
                    Try
                        'Для таблицы
                        Dim strExec As String
                        strExec = String.Format(strSqlTable, r!Description, r!SchemaName, r!TableName)
                        Dim sp As New SqlClient.SqlCommand(strExec, cn)
                        sp.ExecuteNonQuery()
                        tbOutput.AppendText(String.Format("{1}.{2} = '{0}'" & vbCrLf, r!Description, r!SchemaName, r!TableName))
                    Catch ex As Exception
                        tbOutput.AppendText(ex.Message & vbCrLf)
                        Exit For
                    End Try

                Else
                    'Для колонки
                    Try
                        'Для таблицы
                        Dim strExec As String
                        strExec = String.Format(strSqlCloumn, r!Description, r!SchemaName, r!TableName, r!ColumnName)
                        Dim sp As New SqlClient.SqlCommand(strExec, cn)
                        sp.ExecuteNonQuery()
                        tbOutput.AppendText(String.Format("{1}.{2}.{3} = '{0}'" & vbCrLf, r!Description, r!SchemaName, r!TableName, r!ColumnName))
                    Catch ex As Exception
                        tbOutput.AppendText(ex.Message & vbCrLf)
                        Exit For
                    End Try
                End If

            End If
        Next
        cn.Close()
    End Sub

    Private Sub miToXML_Click(sender As System.Object, e As System.EventArgs) Handles miToXML.Click
        Dim fbd As New FolderBrowserDialog
        If fbd.ShowDialog() = DialogResult.OK Then
            dt.WriteXml(String.Format("{0}\{1}.{2}.{3}.xml", fbd.SelectedPath, Replace(miServer.Text, "\", "_"), miDB.Text, TimeToFile()), XmlWriteMode.WriteSchema)
        End If
        If MessageBox.Show("Open folder?", "", MessageBoxButtons.YesNo) = Windows.Forms.DialogResult.Yes Then
            System.Diagnostics.Process.Start("explorer", fbd.SelectedPath)
        End If

    End Sub

    Private Sub miFromXML_Click(sender As System.Object, e As System.EventArgs) Handles miFromXML.Click
        Dim ofd As New OpenFileDialog()
        ofd.Filter = "XML files (*.XML)|*.XML|All files (*.*)|*.*"
        ofd.Multiselect = False
        ofd.RestoreDirectory = True
        If ofd.ShowDialog() = DialogResult.OK Then
            Dim ddt As New DataTable
            ddt.ReadXml(ofd.FileName)
            If ddt.TableName <> dt.TableName Then
                MsgBox(String.Format("File not for this database! Current ='{0}', from file ='{1}'", dt.TableName, ddt.TableName))
            Else
                dt = ddt
                If dt.Columns("Check") Is Nothing Then
                    dt.Columns.Add("Check", GetType(Boolean))
                End If
                dt.Columns("Check").ReadOnly = False
                BindingSource1.DataSource = dt
            End If
        End If

    End Sub

    Private Function TimeToFile() As String
        Dim d As DateTime = DateTime.Now
        Dim res As String = String.Format("{0}_{1}_{2}_{3}", d.Year * 10000 + d.Month * 100 + d.Day, d.Hour, d.Minute, d.Second)
        Return res
    End Function

    Private Sub miReplaceColumn_Click(sender As System.Object, e As System.EventArgs) Handles miReplaceColumn.Click
        If dlgReplaceColumn.ShowDialog = Windows.Forms.DialogResult.OK Then
            Dim ColumnName As String = dlgReplaceColumn.fldColumnName.Text
            Dim Description As String = dlgReplaceColumn.fldDescription.Text
            Dim ReplaceNullOnly As Boolean = dlgReplaceColumn.fldReplaceNullOnly.Checked

            For Each r As DataRow In dt.Rows
                If r!ColumnName IsNot DBNull.Value Then


                    If r!ColumnName = ColumnName Then
                        If ReplaceNullOnly Then
                            If r!Description Is DBNull.Value OrElse r!Description = "" Then
                                r!Description = Description
                            End If
                        Else
                            r!Description = Description
                        End If

                    End If
                End If
            Next


        End If
    End Sub

    Private Sub CheckSelected_Click(sender As System.Object, e As System.EventArgs) Handles CheckSelected.Click
        For Each r As System.Windows.Forms.DataGridViewRow In dgv.SelectedRows
            r.Cells("Check").Value = True
        Next
    End Sub

    Private Sub UncheckSelected_Click(sender As System.Object, e As System.EventArgs) Handles UncheckSelected.Click
        For Each r As System.Windows.Forms.DataGridViewRow In dgv.SelectedRows
            r.Cells("Check").Value = False
        Next
    End Sub

    Private Sub CheckAll_Click(sender As System.Object, e As System.EventArgs) Handles CheckAll.Click
        If dt Is Nothing Then Exit Sub
        For Each r As DataRow In dt.Rows
            r("Check") = True
        Next
    End Sub

    Private Sub UncheckAll_Click(sender As System.Object, e As System.EventArgs) Handles UncheckAll.Click
        If dt Is Nothing Then Exit Sub
        For Each r As DataRow In dt.Rows
            r("Check") = False
        Next
    End Sub

    Private Sub dgv_CellBeginEdit(sender As Object, e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles dgv.CellBeginEdit
        If dgv.Columns(e.ColumnIndex).DataPropertyName = "Description" Then
            dgv.Rows(e.RowIndex).Cells("Check").Value = True
        End If

    End Sub

    'Private mouseDelta As Integer = 0
    'Private Sub dgv_MouseWheel(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgv.MouseWheel
    '    If e.Delta > mouseDelta Then
    '        BindingSource1.MovePrevious()
    '        BindingSource1.MovePrevious()
    '        BindingSource1.MovePrevious()
    '        BindingSource1.MovePrevious()
    '    End If
    '    If e.Delta < mouseDelta Then
    '        BindingSource1.MoveNext()
    '        BindingSource1.MoveNext()
    '        BindingSource1.MoveNext()
    '        BindingSource1.MoveNext()
    '    End If
    '    'mouseDelta = e.Delta
    'End Sub

    Private Sub dgv_RowHeaderMouseDoubleClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles dgv.RowHeaderMouseDoubleClick
        Dim f As New QueryResult
        f.Init(GetConnectionString(True), miDB.Text, dt.Rows(e.RowIndex)!SchemaName, dt.Rows(e.RowIndex)!TableName)

        f.Show()

    End Sub

    'Private Sub miEmtyOnly_Click(sender As System.Object, e As System.EventArgs) Handles miEmtyOnly.Click
    '    If Me.BindingSource1.Filter = "" Then
    '        Me.BindingSource1.Filter = "Isnull(Description,'') = ''"
    '        miEmtyOnly.Text = "All"
    '        ToolStripTextBox1.Text = Me.BindingSource1.Filter
    '    Else
    '        Me.BindingSource1.Filter = ""
    '        miEmtyOnly.Text = "Empty only"
    '        ToolStripTextBox1.Text = Me.BindingSource1.Filter
    '    End If

    'End Sub

    'Private Sub ToolStripTextBox1_TextChanged(sender As Object, e As System.EventArgs) Handles ToolStripTextBox1.TextChanged
    '    Try

    '        Me.BindingSource1.Filter = ToolStripTextBox1.Text
    '    Catch ex As Exception

    '    End Try

    'End Sub

    'Private Sub ToolStripTextBox1_Validated(sender As Object, e As System.EventArgs)
    '    Try

    '        Me.BindingSource1.Filter = ToolStripTextBox1.Text
    '    Catch ex As Exception

    '    End Try
    'End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Try

            Me.BindingSource1.Filter = ToolStripComboBox1.Text
        Catch ex As Exception
            tbOutput.AppendText(ex.Message & vbCrLf)
        End Try

    End Sub


    Private Sub ToolStripComboBox1_KeyUp(sender As Object, e As System.Windows.Forms.KeyEventArgs) Handles ToolStripComboBox1.KeyUp
        If e.KeyData = Keys.Enter Then
            Try

                Me.BindingSource1.Filter = ToolStripComboBox1.Text
            Catch ex As Exception

            End Try
        End If
    End Sub

    Private dtSQLUtilProperty As New DataTable
    Private Sub LoadSQLUtilProperty()
        Button.PerformClick()
        Try
            dtSQLUtilProperty.ReadXml("MS_DescriptionsProperty.xml")
            If dtSQLUtilProperty.Columns!Variable Is Nothing Then
                dtSQLUtilProperty.Columns.Add("Variable")
            End If

            If dtSQLUtilProperty.Columns("Value") Is Nothing Then
                dtSQLUtilProperty.Columns.Add("Value")
            End If

            For Each value As DataRow In dtSQLUtilProperty.Select("Variable='ServerName'")
                miServer.Text = CStr(value!Value)
            Next

            For Each value As DataRow In dtSQLUtilProperty.Select("Variable='UserName'")
                miUser.Text = CStr(value!Value)
            Next

            For Each value As DataRow In dtSQLUtilProperty.Select("Variable='UserPassword'")
                miPassword.Text = CStr(value!Value)
            Next


        Catch ex As Exception

        End Try


    End Sub

    Private Sub SaveSQLUtilProperty()
        Try
            Button.PerformClick()
            dtSQLUtilProperty.TableName = "MS_DescriptionsProperty"

            If dtSQLUtilProperty.Columns!Variable Is Nothing Then
                dtSQLUtilProperty.Columns.Add("Variable")
            End If

            If dtSQLUtilProperty.Columns("Value") Is Nothing Then
                dtSQLUtilProperty.Columns.Add("Value")
            End If

            dtSQLUtilProperty.Clear()

            Dim rServerName As DataRow = dtSQLUtilProperty.NewRow()
            rServerName!Variable = "ServerName"
            rServerName!Value = miServer.Text
            dtSQLUtilProperty.Rows.Add(rServerName)

            Dim rUserName As DataRow = dtSQLUtilProperty.NewRow()
            rUserName!Variable = "UserName"
            rUserName!Value = miUser.Text
            dtSQLUtilProperty.Rows.Add(rUserName)

            Dim rPassword As DataRow = dtSQLUtilProperty.NewRow()
            rPassword!Variable = "UserPassword"
            rPassword!Value = miPassword.Text
            dtSQLUtilProperty.Rows.Add(rPassword)
            dtSQLUtilProperty.WriteXml("MS_DescriptionsProperty.xml", XmlWriteMode.WriteSchema)
        Catch ex As Exception

        End Try
    End Sub

    Private Sub frmMain_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        SaveSQLUtilProperty()
    End Sub
               

    Private Sub frmMain_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        LoadSQLUtilProperty()
    End Sub
End Class
